--    Program: 		CAPPOEXT_PURCHASE_OPTION.sql
--    Description:	Extract PO Purchase Option information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_set_of_books_id		number;
   l_ngo_sob                varchar2(150);
   
   cursor cur_purchase(p_set_books_id number) is
      select psp.* 
      from   po_system_parameters_all psp,
             hr_organization_information hoi 
      where  psp.org_id = hoi.organization_id  
      and    hoi.org_information_context = 'Operating Unit Information'
      and    hoi.org_information3 = to_char(p_set_books_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_PURCHASE_OPTION.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
   end;      

   for s in cur_purchase(l_set_of_books_id) loop   	
  	
     -- output data
     utl_file.put_line( l_utl_file,
      s.LAST_UPDATE_DATE                || chr(9) || 
      s.LAST_UPDATED_BY                 || chr(9) ||
      s.LAST_UPDATE_LOGIN               || chr(9) || 
      s.CREATION_DATE                   || chr(9) ||
      s.CREATED_BY                      || chr(9) ||
      s.ACCRUED_CODE_COMBINATION_ID     || chr(9) ||
      s.PRICE_CHANGE_ALLOWANCE          || chr(9) ||
      s.REQ_CAN_AUTHORIZE               || chr(9) ||
      s.INSPECTION_REQUIRED_FLAG        || chr(9) ||
      s.ENFORCE_PRICE_CHANGE_ALLOWANCE  || chr(9) ||
      s.DEFAULT_RATE_TYPE               || chr(9) ||
      s.TAXABLE_FLAG                    || chr(9) ||
      s.USER_DEFINED_RECEIPT_NUM_CODE   || chr(9) ||
      s.MANUAL_RECEIPT_NUM_TYPE         || chr(9) ||
      s.USER_DEFINED_QUOTE_NUM_CODE     || chr(9) ||
      s.MANUAL_QUOTE_NUM_TYPE           || chr(9) ||
      s.USER_DEFINED_RFQ_NUM_CODE       || chr(9) ||
      s.MANUAL_RFQ_NUM_TYPE             || chr(9) ||
      s.USER_DEFINED_PO_NUM_CODE        || chr(9) ||
      s.MANUAL_PO_NUM_TYPE              || chr(9) ||
      s.USER_DEFINED_REQ_NUM_CODE       || chr(9) ||
      s.MANUAL_REQ_NUM_TYPE             || chr(9) ||
      s.DEFAULT_QUOTE_WARNING_DELAY     || chr(9) ||
      s.ENFORCE_BUYER_AUTHORITY_FLAG    || chr(9) ||
      s.ENFORCE_BUYER_NAME_FLAG         || chr(9) ||
      s.ENFORCE_VENDOR_HOLD_FLAG        || chr(9) ||
      s.NOTIFY_IF_BLANKET_FLAG          || chr(9) ||
      s.RFQ_REQUIRED_FLAG               || chr(9) ||
      s.PO_DOCUMENT_REPORT_ID           || chr(9) ||
      s.PO_DOCUMENT_PRINT_FLAG          || chr(9) ||
      s.RFQ_DOCUMENT_REPORT_ID          || chr(9) ||
      s.RFQ_DOCUMENT_PRINT_FLAG         || chr(9) ||
      s.LINE_TYPE_ID                    || chr(9) ||
      s.NEXT_APPROVER_LOOKUP_CODE       || chr(9) ||
      s.RECEIVING_FLAG                  || chr(9) ||
      s.ALLOW_ITEM_DESC_UPDATE_FLAG     || chr(9) ||
      s.DISPOSITION_WARNING_FLAG        || chr(9) ||
      s.ENFORCE_FULL_LOT_QUANTITIES     || chr(9) ||
      s.SECURITY_POSITION_STRUCTURE_ID  || chr(9) ||
      s.MIN_RELEASE_AMOUNT              || chr(9) ||
      s.PRICE_TYPE_LOOKUP_CODE          || chr(9) ||
      s.NOTIFICATION_WARNING_DELAY      || chr(9) ||
      s.PRICE_BREAK_LOOKUP_CODE         || chr(9) ||
      s.INVOICE_CLOSE_CODE              || chr(9) ||
      s.RECEIVE_CLOSE_CODE              || chr(9) ||
      s.INVOICE_CLOSE_TOLERANCE         || chr(9) ||
      s.RECEIVE_CLOSE_TOLERANCE         || chr(9) ||
      s.CANCEL_REQS_ON_PO_CANCEL_FLAG   || chr(9) ||
      s.REQUEST_ID                      || chr(9) ||
      s.PROGRAM_APPLICATION_ID          || chr(9) ||
      s.PROGRAM_ID                      || chr(9) ||
      s.PROGRAM_UPDATE_DATE             || chr(9) ||
      s.REQIMPORT_GROUP_BY_CODE         || chr(9) ||
      s.EXPENSE_ACCRUAL_CODE            || chr(9) ||
      s.INVENTORY_ACCRUAL_CODE          || chr(9) ||
      s.ORDER_TYPE_ID                   || chr(9) ||
      s.ORDER_SOURCE_ID                 || chr(9) ||
      s.SALESREP_ID                     || chr(9) ||
      s.TERM_ID                         || chr(9) ||
      s.ORG_ID                          || chr(9) ||
      s.GLOBAL_ATTRIBUTE1               || chr(9) ||
      s.GLOBAL_ATTRIBUTE2               || chr(9) ||
      s.GLOBAL_ATTRIBUTE3               || chr(9) ||
      s.GLOBAL_ATTRIBUTE4               || chr(9) ||
      s.GLOBAL_ATTRIBUTE5               || chr(9) ||
      s.GLOBAL_ATTRIBUTE6               || chr(9) ||
      s.GLOBAL_ATTRIBUTE7               || chr(9) ||
      s.GLOBAL_ATTRIBUTE8               || chr(9) ||
      s.GLOBAL_ATTRIBUTE9               || chr(9) ||
      s.GLOBAL_ATTRIBUTE10              || chr(9) ||
      s.GLOBAL_ATTRIBUTE11              || chr(9) ||
      s.GLOBAL_ATTRIBUTE12              || chr(9) ||
      s.GLOBAL_ATTRIBUTE13              || chr(9) ||
      s.GLOBAL_ATTRIBUTE14              || chr(9) ||
      s.GLOBAL_ATTRIBUTE15              || chr(9) ||
      s.GLOBAL_ATTRIBUTE16              || chr(9) ||
      s.GLOBAL_ATTRIBUTE17              || chr(9) ||
      s.GLOBAL_ATTRIBUTE18              || chr(9) ||
      s.GLOBAL_ATTRIBUTE19              || chr(9) ||
      s.GLOBAL_ATTRIBUTE20              || chr(9) ||
      s.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
      s.TAX_FROM_SHIP_TO_LOC_FLAG       || chr(9) ||
      s.TAX_FROM_ITEM_FLAG              || chr(9) ||
      s.TAX_FROM_VENDOR_FLAG            || chr(9) ||
      s.TAX_FROM_VENDOR_SITE_FLAG       || chr(9) ||
      s.TAX_FROM_SYSTEM_FLAG            || chr(9) ||
      s.TAX_HIER_SHIP_TO_LOC            || chr(9) ||
      s.TAX_HIER_ITEM                   || chr(9) ||
      s.TAX_HIER_VENDOR                 || chr(9) ||
      s.TAX_HIER_VENDOR_SITE            || chr(9) ||
      s.TAX_HIER_SYSTEM                 || chr(9) ||
      s.CREATE_DEBIT_MEMO_FLAG          || chr(9) ||
      s.ENFORCE_PRICE_CHANGE_AMOUNT     || chr(9) ||
      s.PRICE_CHANGE_AMOUNT                             
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob||': Purchase Option not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOEXT_PURCHASE_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
